


ALTER   proc sp_is_order_approved
@salesOrder_id int,
@customer_email varchar(50) OUTPUT

as

declare @pCursor CURSOR
declare @lineItem_id int
declare @is_scheduled varchar(50) 

begin
set @pCursor = CURSOR FOR SELECT LineItem_ID FROM Customer_Open_Order_LineItem WHERE 
	SalesOrder_ID = @salesOrder_id

OPEN @pCursor
	
FETCH next FROM @pCursor INTO @lineItem_id 
WHILE @@fetch_status = 0
begin
	select @is_scheduled = (select cool_schedule_date from customer_open_order_lineitem
				 where lineItem_id = @lineItem_id)
	
	if (@is_scheduled is null)
	begin
		return 1
	end

	if exists (SELECT * FROM LineItem_Hold_Table WHERE
		 LineItem_ID = @lineItem_id and Is_Approved = 1)		
	begin	
		return 1
	end

	if exists (SELECT * FROM Sales_Order_Hold_Table WHERE
		SalesOrder_ID = @salesOrder_id and Is_Approved = 1)
	begin		
		return 1
	end
	

	
	FETCH next FROM @pCursor INTO @lineItem_id 		
end
	close @pCursor
	deallocate @pCursor


declare @address_id int 

select @address_id = (SELECT SO_Shipping_Address_ID FROM Sales_Order WHERE 
	SalesOrder_ID = @salesOrder_id)

select @customer_email = (SELECT Email FROM Address WHERE
	Address_ID = @address_id)
	
return 0
end


